from config_data.config import host, username, password, db_name
from db.objects import *
import datetime
import random
import string

import psycopg2


class PGConnector:
    """Класс для создания подключения к БД"""

    def __init__(self):
        self.host = host
        self.username = username
        self.password = password
        self.db_name = db_name

    def connect(self):
        return psycopg2.connect(
            host=self.host,
            user=self.username,
            password=self.password,
            dbname=self.db_name
        )


class PGFamily(PGConnector):
    """Класс для работы с таблицей t_family"""

    def __init__(self):
        self.pg_con = PGConnector().connect()
        self.pg_fu = PGFamilyUser()

    def __generate_hash_link(self, length: int = 12):
        """Метод для генерации hash_link - случайная последовательность букв, цифр и символов"""

        characters = string.ascii_letters + string.digits + string.punctuation
        password = ''.join(random.choice(characters) for i in range(length))

        return password

    @staticmethod
    def get_family_id_by_hash(cursor, hash_link: str) -> int:
        """Выдает family_id по hash_link"""

        q_select_family_id = f"SELECT family_id FROM {t_family} WHERE hash_link = %s;"
        cursor.execute(q_select_family_id, [hash_link])

        result = cursor.fetchall()
        result = result[0][0] if result else None
        print(f"[INFO] - get family_id = {result}")

        return result

    @staticmethod
    def get_hash_link_by_id(cursor, family_id: int) -> str:
        """Выдает hash_link по family_id"""

        q_select_hash_link = f"SELECT hash_link FROM {t_family} WHERE family_id = %s;"
        cursor.execute(q_select_hash_link, [family_id])

        hash_link = cursor.fetchall()[0][0]
        print(f"[INFO] - get hash_link = {hash_link}")

        return hash_link

    def get_hash_link_for_user(self, user_id: int) -> str:
        """Выдает hash_link активной семьи у пользователя (КНОПКА)"""

        try:
            cursor = self.pg_con.cursor()
            hash_link = None

            active_family_id = PGFamilyUser.get_active_family_id(cursor, user_id)

            if active_family_id:
                hash_link = PGFamily.get_hash_link_by_id(cursor, active_family_id)

            else:
                print(f"[WARNING] - user '{user_id}' has not active family for get hash_link")

            self.pg_con.commit()

            cursor.close()
            self.pg_con.close()

            return hash_link

        except (psycopg2.Error, ValueError) as e:
            self.pg_con.rollback()
            print("[ERROR] - Error: ", str(e))

            with open('logs.txt', 'a') as file:
                dt = datetime.datetime.now()
                file.write(f'{dt} [ERROR] - Error: {str(e)}\n')

    def is_family_name_exists(self, family_name: str, user_id: int) -> bool:
        """Метод, который проверяет наличие семьи с таким названием у пользователя"""

        families = self.pg_fu.get_families_info(user_id)

        if family_name in families.values():
            return True
        return False

    def create_family(self, family_name: str, user_id: int) -> bool:
        """
        Описание:
        Создание семьи в таблице t_family (КНОПКА).
        :family_name - название семьи, которое вводит пользователь;
        :hash_link - 12-и символьная ссылка-приглашение, которую формирует скрипт (случайным образом);
        :user_id - id пользователя, который нажал на кнопку.

        Принцип работы:
        Вначале проверяется существование семьи с переданным названием у пользователя.
        Если совпадений не найдено, создается новая запись в таблице t_family и t_family_user соответственно.
        Возвращает True, если семья создана. Иначе False.
        """

        try:
            if not self.is_family_name_exists(family_name, user_id):

                cursor = self.pg_con.cursor()

                hash_link = self.__generate_hash_link()

                q_insert_family = f"INSERT INTO {t_family} (family_name, hash_link) VALUES (%s, %s);"
                cursor.execute(q_insert_family, [family_name, hash_link])

                print(f"[INFO] - created family '{family_name}' for user '{user_id}'")

                family_id = self.get_family_id_by_hash(cursor, hash_link)

                self.pg_fu.create_family_user(cursor, family_id, user_id, True)

                self.pg_con.commit()

                cursor.close()
                self.pg_con.close()

                return True

            else:
                print(f"[WARNING] - family '{family_name}' for user '{user_id}' is already exist")

        except (psycopg2.Error, ValueError) as e:
            self.pg_con.rollback()
            print("[ERROR] - Error: ", str(e))

            with open('logs.txt', 'a') as file:
                dt = datetime.datetime.now()
                file.write(f'{dt} [ERROR] - Error: {str(e)}\n')


class PGUser(PGConnector):
    """Класс для работы с таблицей t_user"""

    def __init__(self):
        self.pg_con = PGConnector().connect()

    def is_user_exist(self, cursor, user_id: int) -> bool:
        """Метод проверящий наличие пользователя в таблице t_user (True - существует, False - отсутствует)"""

        q_check_user_exist = f"""
            SELECT EXISTS(
                SELECT * 
                FROM {t_user}
                WHERE user_id = %s
            );
        """

        cursor.execute(q_check_user_exist, [user_id])
        return cursor.fetchone()[0]

    def create_user(self, user_id: int, nickname: str) -> bool:
        """Метод добавляющий пользователя в таблицу t_user."""

        try:
            cursor = self.pg_con.cursor()
            result = False

            if not self.is_user_exist(cursor, user_id):
                q_insert_user = f"INSERT INTO {t_user} VALUES (%s, %s);"
                cursor.execute(q_insert_user, [user_id, nickname])

                print(f"[INFO] - created user '{user_id}' with nickname '{nickname}'")
                result = True

            else:
                print(f"[WARNING] - user '{user_id}' is already exist")

            self.pg_con.commit()

            cursor.close()
            self.pg_con.close()

            return result

        except (psycopg2.Error, ValueError) as e:
            self.pg_con.rollback()
            print("[ERROR] - Error: ", str(e))

            with open('logs.txt', 'a') as file:
                dt = datetime.datetime.now()
                file.write(f'{dt} [ERROR] - Error: {str(e)}\n')


class PGFamilyUser(PGConnector):
    """Класс для работы с таблицей t_family_user"""

    def __init__(self):
        self.pg_con = PGConnector().connect()

    @staticmethod
    def get_active_family_user_id(cursor, user_id: int) -> int:
        """Метод для получения активного family_user_id (активной юзер-семьи)"""

        q_select_active_family = f"""
                SELECT family_user_id 
                FROM {t_family_user}
                WHERE is_active = True
                    AND user_id = %s
            """

        cursor.execute(q_select_active_family, [user_id])

        active_family_user_id = cursor.fetchone()
        active_family_user_id = active_family_user_id[0] if active_family_user_id else None

        print(f"[INFO] - user '{user_id} has active family_user_id '{active_family_user_id}'")

        return active_family_user_id

    @staticmethod
    def get_active_family_id(cursor, user_id: int):
        """Метод для получения активного family_id (id активной семьи у пользователя)"""

        q_select_active_family = f"""
                        SELECT family_id 
                        FROM {t_family_user}
                        WHERE is_active = True
                            AND user_id = %s
                    """

        cursor.execute(q_select_active_family, [user_id])

        active_family_id = cursor.fetchone()
        active_family_id = active_family_id[0] if active_family_id else None

        print(f"[INFO] - user '{user_id} has active family_id '{active_family_id}'")

        return active_family_id

    def is_family_user_exist(self, cursor, family_id: int, user_id: int) -> bool:
        """Проверка существования связки family_id - user_id (состоит ли пользователь в данной семье)"""

        q_check_exists_family = f"""
            WITH prep_t_family_user as (
                select family_id
                from {t_family_user}
                where user_id = %s
            )
            SELECT EXISTS(
                SELECT * 
                FROM prep_t_family_user ptfu
                JOIN {t_family} tf ON ptfu.family_id = tf.family_id
                WHERE tf.family_id = %s
            );
        """

        cursor.execute(q_check_exists_family, [user_id, family_id])
        return cursor.fetchone()[0]

    def create_family_user(self, cursor, family_id: int, user_id: int, is_creator=False):
        """
        Метод для создания записи family_user_id.
        Вызывается при создании семьи (create_family) или при вступлении в семью (join_family).
        """

        q_insert_family = f"""
            INSERT INTO {t_family_user} (family_id, user_id, is_active, is_creator) 
            VALUES (%s, %s, %s, %s);
        """
        cursor.execute(q_insert_family, [family_id, user_id, False, is_creator])

        print(f"[INFO] - created family_user with family_id '{family_id}' and user_id = '{user_id}'")

        self.upd_active_family(cursor, family_id, user_id)

    def join_family(self, hash_link: str, user_id: int) -> bool:
        """Метод реализующий вступление пользователя в существующую семью по hash_link (КНОПКА)"""

        try:
            cursor = self.pg_con.cursor()
            result = False

            family_id = PGFamily.get_family_id_by_hash(cursor, hash_link)

            if not family_id:
                raise ValueError("cannot join_family - family_id is None")

            if not self.is_family_user_exist(cursor, family_id, user_id):
                self.create_family_user(cursor, family_id, user_id, is_creator=False)

                print(f"[INFO] - user '{user_id}' has successfully joined the family '{family_id}'")

                result = True

            else:
                print(f"[WARNING] - user '{user_id}' is already in this family")

            self.pg_con.commit()

            cursor.close()
            self.pg_con.close()

            return result

        except (psycopg2.Error, ValueError) as e:
            self.pg_con.rollback()
            print("[ERROR] - Error: ", str(e))

            with open('logs.txt', 'a') as file:
                dt = datetime.datetime.now()
                file.write(f'{dt} [ERROR] - Error: {str(e)}\n')

    def get_families_info(self, user_id: int) -> dict:
        """
        Метод, который выводит информацию для пользователя о всех его семьях (КНОПКА).
        Возвращает словарь формата: {'family_id': family_name}.
        """

        try:
            cursor = self.pg_con.cursor()
            family_info = dict()

            q_select_family = f"""
                WITH prep_t_family_user as (
                    select family_id
                    from {t_family_user}
                    where user_id = %s
                )
                SELECT tf.family_id, tf.family_name
                FROM {t_family} tf
                JOIN prep_t_family_user ptfu
                    ON tf.family_id = ptfu.family_id;
            """

            cursor.execute(q_select_family, [user_id])

            family_info = cursor.fetchall()
            family_info = {tup[0]: tup[1] for tup in family_info}

            print(f"[INFO] - family for user '{user_id}': {family_info}")
            self.pg_con.commit()

            cursor.close()
            self.pg_con.close()

            return family_info

        except (psycopg2.Error, ValueError) as e:
            self.pg_con.rollback()
            print("[ERROR] - Error: ", str(e))

            with open('logs.txt', 'a') as file:
                dt = datetime.datetime.now()
                file.write(f'{dt} [ERROR] - Error: {str(e)}\n')

    def make_family_active(self, family_id: int, user_id: int) -> bool:
        """Метод реализующий выбор активной семьи для пользователя по family_id (КНОПКА)"""

        try:
            cursor = self.pg_con.cursor()
            result = False

            if self.is_family_user_exist(cursor, family_id, user_id):
                self.upd_active_family(cursor, family_id, user_id)
                result = True

            else:
                print(f"[WARNING] - family '{family_id}' for user '{user_id}' was not found")

            self.pg_con.commit()

            cursor.close()
            self.pg_con.close()

            return result

        except (psycopg2.Error, ValueError) as e:
            self.pg_con.rollback()
            print("[ERROR] - Error: ", str(e))

            with open('logs.txt', 'a') as file:
                dt = datetime.datetime.now()
                file.write(f'{dt} [ERROR] - Error: {str(e)}\n')

    def upd_active_family(self, cursor, family_id: int, user_id: int):
        """Метод для обновления активной семьи у пользователя"""

        q_update_active_family = f"""
            UPDATE {t_family_user}
            SET is_active = False
            WHERE user_id = %s;

            UPDATE {t_family_user}
            SET is_active = True
            WHERE family_id = %s AND user_id = %s;
        """

        cursor.execute(q_update_active_family, [user_id, family_id, user_id])
        print(f"[INFO] - now family '{family_id}' for user '{user_id}' is active")


class PGPurchase(PGConnector):
    """Класс для работы с таблицей t_purchase"""

    def __init__(self):
        self.pg_con = PGConnector().connect()

    def create_purchase(self, user_id: int, purchase_amount: float, shop_name: str = None) -> bool:
        """Метод для внесения информации о покупке пользователем в активную семью (КНОПКА)"""

        try:
            cursor = self.pg_con.cursor()
            result = False

            active_family_user_id = PGFamilyUser.get_active_family_user_id(cursor, user_id)
            dt = datetime.datetime.now()

            if active_family_user_id:
                q_insert_purchase = f"""
                    INSERT INTO {t_purchase} (family_user_id, purchase_amount, shop_name, create_dttm)
                    VALUES (%s, %s, %s, %s)
                """

                cursor.execute(q_insert_purchase, [active_family_user_id, purchase_amount, shop_name, dt])
                print(f"[INFO] - purchase for user '{user_id}' has been successfully recorded")

                result = True

            else:
                print(f"[WARNING] - active_family_user_id for user '{user_id}' was not found")

            self.pg_con.commit()

            cursor.close()
            self.pg_con.close()

            return result

        except (psycopg2.Error, ValueError) as e:
            self.pg_con.rollback()
            print("[ERROR] - Error: ", str(e))

            with open('logs.txt', 'a') as file:
                dt = datetime.datetime.now()
                file.write(f'{dt} [ERROR] - Error: {str(e)}\n')

    def get_info_expenses(self, user_id: int) -> dict:
        """Метод, который выводит сводную информацию о тратах в семье за последний месяц (КНОПКА)"""

        try:
            cursor = self.pg_con.cursor()
            info_expenses = dict()

            active_family_id = PGFamilyUser.get_active_family_id(cursor, user_id)

            if active_family_id:
                q_select_family_purchase = f"""
                    WITH prep_family_user AS (
                        SELECT 
                            tfu.family_user_id,
                            tu.nickname
                        FROM {t_family_user} tfu
                        JOIN {t_user} tu
                            ON tfu.user_id = tu.user_id
                        WHERE family_id = %s
                    ),
                    info_expenses AS (
                        SELECT 
                            pfu.nickname,
                            SUM(purchase_amount) as sum_purchase_amount
                        FROM {t_purchase} tp
                        JOIN prep_family_user pfu
                            ON tp.family_user_id = pfu.family_user_id
                        WHERE 1 = 1
                            AND tp.create_dttm >= DATE_TRUNC('month', CURRENT_DATE)
                            AND tp.create_dttm < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month'
                        GROUP BY pfu.nickname
                        ORDER BY pfu.nickname
                    )
                    SELECT 
                        nickname,
                        sum_purchase_amount
                    FROM info_expenses
                    UNION
                    SELECT 
                        'ИТОГ' as nickname,
                        SUM(sum_purchase_amount) AS sum_purchase_amount
                    FROM info_expenses
                """

                cursor.execute(q_select_family_purchase, [active_family_id])

                info_expenses = cursor.fetchall()
                info_expenses = {name: float(amount) for name, amount in info_expenses}

                print(f"[INFO] - info_expenses for family_id = '{active_family_id}': {info_expenses}")

            else:
                print(f"[WARNING] - active_family_user_id for user '{user_id}' was not found")

            self.pg_con.commit()

            cursor.close()
            self.pg_con.close()

            return info_expenses

        except (psycopg2.Error, ValueError) as e:
            self.pg_con.rollback()
            print("[ERROR] - Error: ", str(e))

            with open('logs.txt', 'a') as file:
                dt = datetime.datetime.now()
                file.write(f'{dt} [ERROR] - Error: {str(e)}\n')

    def get_info_weeks_expenses(self, week: int, user_id: int) -> list:
        """
        Метод который выводит информацию о покупках пользователя за неделю.

        Возвращает список из кортежей, например:
            [('2024-05-17 02:27:41', 100.0, None), ('2024-05-17 02:27:41', 700.0, 'Пятера')]
        """

        try:
            cursor = self.pg_con.cursor()
            info_weeks_expenses = list()

            active_family_user_id = PGFamilyUser.get_active_family_user_id(cursor, user_id)

            if active_family_user_id:
                q_select_purchases = f"""
                    SELECT
                        create_dttm,
                        purchase_amount,
                        shop_name
                    FROM {t_purchase}
                    WHERE family_user_id = %s
                """

                if week == 1:
                    q_select_purchases += ''' AND create_dttm >= CURRENT_DATE - INTERVAL '7 days';'''
                elif week == 2:
                    q_select_purchases += ''' AND create_dttm >= CURRENT_DATE - INTERVAL '14 days'
                                                                AND create_dttm < CURRENT_DATE - INTERVAL '7 days';'''
                elif week == 3:
                    q_select_purchases += ''' AND create_dttm >= CURRENT_DATE - INTERVAL '21 days'
                                                                AND create_dttm < CURRENT_DATE - INTERVAL '14 days';'''
                elif week == 4:
                    q_select_purchases += ''' AND create_dttm >= CURRENT_DATE - INTERVAL '28 days'
                                                                AND create_dttm < CURRENT_DATE - INTERVAL '21 days';'''

                cursor.execute(q_select_purchases, [active_family_user_id])

                info_weeks_expenses = cursor.fetchall()

                if info_weeks_expenses:
                    info_weeks_expenses = [(item[0].strftime("%Y-%m-%d %H:%M:%S"), float(item[1]), item[2]) for item
                                       in info_weeks_expenses]

                print(f"[INFO] - info_expenses for user_id '{user_id}' from week = {week}: {info_weeks_expenses}")

            else:
                print(f"[WARNING] - active_family_user_id for user '{user_id}' was not found")

            self.pg_con.commit()

            cursor.close()
            self.pg_con.close()

            return info_weeks_expenses

        except (psycopg2.Error, ValueError) as e:
            self.pg_con.rollback()
            print("[ERROR] - Error: ", str(e))

            with open('logs.txt', 'a') as file:
                dt = datetime.datetime.now()
                file.write(f'{dt} [ERROR] - Error: {str(e)}\n')